
import pandas as pd
from pymongo import MongoClient
import json
# 读取Excel文件，指定工作表
FILEPATH = "test.xlsx"
SHEETNAMES = ['Company-Level', 'Provincial-Level', 'Group-Level']


def add_team_members(file, sheet_name):
    # 读取 Excel 文件，指定工作表
    df = pd.read_excel(file, sheet_name=sheet_name)

    # 按照奖项进行分组
    grouped = df.groupby('奖项')
    df['团队成员'] = ''

    for award, group in grouped:
        # 获取当前组的所有成员
        team_members = group['姓名'].tolist()
        # 如果奖项是“优秀组织奖”，则跳过团队成员的添加
        if award == "优秀组织奖":
            continue
        # 为每个成员分配团队成员列表
        for index, row in group.iterrows():
            other_members = team_members.copy()
            # # 过滤掉当前成员，获取其他成员
            # other_members = [
            #     member for member in team_members if member != row['姓名']]
            # 将列表转换为字符串
            df.loc[index, '团队成员'] = ', '.join(other_members)

    return df


def transform_excel_to_mongo_format(result_df):
    # 读取Excel文件，指定工作表
    df = result_df
    # 按照姓名进行分组
    grouped = df.groupby('邮箱')
    result = []
    for userEmail, group in grouped:
        sub_result = {

            "userEmail": userEmail,
            "awardDetails": []
        }
        for _, row in group.iterrows():
            award_info = {
                "姓名": row['姓名'],
                "奖项": row['奖项'],
                "等级": row['等级'],
                "奖励科目": row['奖励科目'],
                "颁发机构": row['颁发机构'],
                # "团队成员": row['团队成员'],
                "获奖时间": row['获奖时间'],
            }
            sub_result["awardDetails"].append(award_info)
        result.append(sub_result)
    return result
# 另存成 json文件


def save_data_to_json(data, filename):
    with open(filename, 'w', encoding='utf-8') as json_file:
        json.dump(data, json_file, ensure_ascii=False, indent=4)
    print(f"数据已成功保存为 {filename}")

# 写入到数据库中


def insert_data_to_mongo(data, file):
    client = MongoClient('mongodb://localhost:27017/')
    db = client['awards']  # 如果数据库不存在，会在插入数据时自动创建
    colName = file
    collection = db[colName]  # 如果集合不存在，会在插入数据时自动创建
    collection.insert_many(data)  # 插入数据
    print("数据已成功插入到MongoDB中")
    client.close()  # 关闭连接


if __name__ == "__main__":
    for sheetname in SHEETNAMES:
        result_df = add_team_members(FILEPATH,sheetname)
        data = transform_excel_to_mongo_format(result_df)  # 替换为实际的工作表名称
        print(data)
        # save_data_to_json(
        #     data, f"{sheetname}_data.json")  # 保存为 JSON 文件
        insert_data_to_mongo(data,sheetname)  # 插入数据到MongoDB


# filePath = ["/home/first-project/excelToMongodb/InnovateAtWork.xlsx"]

# for file in filePath:
#     data = file.split('/')[-1].split('.')[0]
#     print(data)
